﻿Imports System.Collections.Generic
Imports System.Data
Imports System.IO
Imports System.Web
Imports NPOI
Imports NPOI.HPSF
Imports NPOI.HSSF
Imports NPOI.HSSF.UserModel
Imports NPOI.POIFS
Imports NPOI.Util

Public Class DataTableRenderToExcel
    Public Shared Function RenderDataTableToExcel(ByVal SourceTable As DataTable) As Stream
        Dim workbook As New HSSFWorkbook()
        Dim ms As New MemoryStream()
        Dim sheet As HSSFSheet = DirectCast(workbook.CreateSheet(), HSSFSheet)
        Dim headerRow As HSSFRow = DirectCast(sheet.CreateRow(0), HSSFRow)

        ' handling header.
        For Each column As DataColumn In SourceTable.Columns
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
        Next

        ' handling value.
        Dim rowIndex As Integer = 1

        For Each row As DataRow In SourceTable.Rows
            Dim dataRow As HSSFRow = DirectCast(sheet.CreateRow(rowIndex), HSSFRow)
            Dim tmp As Double
            For Each column As DataColumn In SourceTable.Columns
                If column.DataType Is System.Type.[GetType]("System.Decimal") Then
                    Try
                        tmp = Double.Parse(row(column).ToString())
                        dataRow.CreateCell(column.Ordinal).SetCellValue(tmp)
                    Catch
                        dataRow.CreateCell(column.Ordinal).SetCellValue("")



                    End Try
                Else
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row(column).ToString())
                End If
            Next

            rowIndex += 1
        Next

        workbook.Write(ms)
        ms.Flush()
        ms.Position = 0

        sheet = Nothing
        headerRow = Nothing
        workbook = Nothing

        Return ms
    End Function

    Public Shared Sub RenderDataTableToExcel(ByVal SourceTable As DataTable, ByVal FileName As String)
        Dim ms As MemoryStream = TryCast(RenderDataTableToExcel(SourceTable), MemoryStream)
        Dim fs As New FileStream(FileName, FileMode.Create, FileAccess.Write)
        Dim data As Byte() = ms.ToArray()

        fs.Write(data, 0, data.Length)
        fs.Flush()
        fs.Close()

        data = Nothing
        ms = Nothing
        fs = Nothing
    End Sub

    Public Shared Function RenderDataTableFromExcel(ByVal ExcelFileStream As Stream, ByVal SheetName As String, ByVal HeaderRowIndex As Integer) As DataTable
        Dim workbook As New HSSFWorkbook(ExcelFileStream)
        Dim sheet As HSSFSheet = DirectCast(workbook.GetSheet(SheetName), HSSFSheet)

        Dim table As New DataTable()

        Dim headerRow As HSSFRow = DirectCast(sheet.GetRow(HeaderRowIndex), HSSFRow)
        Dim cellCount As Integer = headerRow.LastCellNum

        For i As Integer = headerRow.FirstCellNum To cellCount - 1
            Dim column As New DataColumn(headerRow.GetCell(i).StringCellValue)
            table.Columns.Add(column)
        Next

        Dim rowCount As Integer = sheet.LastRowNum

        For i As Integer = (sheet.FirstRowNum + 1) To sheet.LastRowNum - 1
            Dim row As HSSFRow = DirectCast(sheet.GetRow(i), HSSFRow)
            Dim dataRow As DataRow = table.NewRow()

            For j As Integer = row.FirstCellNum To cellCount - 1
                dataRow(j) = row.GetCell(j)
            Next
        Next

        ExcelFileStream.Close()
        workbook = Nothing
        sheet = Nothing
        Return table
    End Function

    Public Shared Function RenderDataTableFromExcel(ByVal ExcelFileStream As Stream, ByVal SheetIndex As Integer, ByVal HeaderRowIndex As Integer) As DataTable
        Dim workbook As New HSSFWorkbook(ExcelFileStream)
        Dim sheet As HSSFSheet = DirectCast(workbook.GetSheetAt(SheetIndex), HSSFSheet)

        Dim table As New DataTable()

        Dim headerRow As HSSFRow = DirectCast(sheet.GetRow(HeaderRowIndex), HSSFRow)
        Dim cellCount As Integer = headerRow.LastCellNum

        For i As Integer = headerRow.FirstCellNum To cellCount - 1
            Dim column As New DataColumn(headerRow.GetCell(i).StringCellValue)
            table.Columns.Add(column)
        Next

        Dim rowCount As Integer = sheet.LastRowNum

        For i As Integer = (sheet.FirstRowNum + 1) To sheet.LastRowNum - 1
            Dim row As HSSFRow = DirectCast(sheet.GetRow(i), HSSFRow)
            Dim dataRow As DataRow = table.NewRow()

            For j As Integer = row.FirstCellNum To cellCount - 1
                If row.GetCell(j) IsNot Nothing Then
                    dataRow(j) = row.GetCell(j)
                End If
            Next

            table.Rows.Add(dataRow)
        Next

        ExcelFileStream.Close()
        workbook = Nothing
        sheet = Nothing
        Return table
    End Function
End Class